﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace AutoCreateCodePlus
{
    public static class DbHelper
    {
        public static string MainDB_ConnectString = string.Empty;
        public static SqlConnection OpenConn()
        {
            var conn = new SqlConnection(MainDB_ConnectString);
            conn.Open();
            return conn;
        }
        public static int ExecuteNonQuery(this SqlConnection conn, string sql, Dictionary<string, object> param)
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                if (param != null)
                {
                    foreach (var item in param)
                    {
                        cmd.Parameters.AddWithValue(item.Key, item.Value ?? DBNull.Value);
                    }
                }
                cmd.CommandText = sql;
                return cmd.ExecuteNonQuery();
            }
        }
        public static object ExecuteScalar(this SqlConnection conn, string sql, Dictionary<string, object> param)
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                if (param != null)
                {
                    foreach (var item in param)
                    {
                        cmd.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                cmd.CommandText = sql;
                return cmd.ExecuteScalar();
            }
        }

        public static void ExecuteReader(this SqlConnection conn, string sql, Dictionary<string, object> param, Action<SqlDataReader> action)
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                if (param != null)
                {
                    foreach (var item in param)
                    {
                        cmd.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                cmd.CommandText = sql;
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    action(reader);
                }
                reader.Close();
            }
        }

        private static void test()
        {
            //ExecuteNonQuery
            using (var conn = DbHelper.OpenConn())
            {
                var paramMap = new Dictionary<string, object>() {
                    {"@Name","123" },
                    {"@Id","123" },
                };
                var ret = conn.ExecuteNonQuery("update t1 set name = @Name where Id=@Id", paramMap);
            }


            //ExecuteReader
            using (var conn = DbHelper.OpenConn())
            {
                var map = new Dictionary<string, object>() {
                    {"@Name","123" },
                    {"@Id","123" },
                };
                List<string> list = new List<string>();
                conn.ExecuteReader("select * from t1 where Id = @Id", map, (rdr) =>
                {
                    var item = rdr["Id"].ToString();
                    list.Add(item);
                });
            }

            //Transaction
            using (var conn = DbHelper.OpenConn())
            {
                using (var trans = conn.BeginTransaction())
                {
                    var paramMap = new Dictionary<string, object>() {
                        {"@Name","123" },
                        {"@Id","123" },
                    };
                    var ret = conn.ExecuteNonQuery("update t1 set name = @Name where Id=@Id", paramMap);

                    if (ret != 1)
                    {
                        trans.Rollback();
                    }
                    var ret2 = conn.ExecuteNonQuery("update t1 set name = @Name where Id=@Id", paramMap);


                    trans.Commit();

                }

            }
        }

    }
}
